from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, PatternFill, Border, Side
from openpyxl.utils import get_column_letter
import os

def remove_file(file_path: str):
    if os.path.exists(file_path):
        os.remove(file_path)


def create_material_template_0805(output_path, new_data=None):
    """
    创建物资领料单模板
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "物资领料单"
    
    # === 设置样式 ===
    title_font = Font(name='宋体', size=14, bold=True)
    header_font = Font(name='宋体', size=11, bold=True)
    normal_font = Font(name='宋体', size=11)
    border = Border(left=Side(style='thin'), 
                   right=Side(style='thin'), 
                   top=Side(style='thin'), 
                   bottom=Side(style='thin'))
    bottom_border = Border(bottom=Side(style='thin'))
    BR_border = Border(bottom=Side(style='thin'), right=Side(style='thin'))
    BL_border = Border(bottom=Side(style='thin'), left=Side(style='thin'))

    center_alignment = Alignment(horizontal='center', vertical='center')
    right_alignment = Alignment(horizontal='right', vertical='center')
    left_alignment = Alignment(horizontal='left', vertical='center')
    
    # === 写入标题行 ===
    ws.row_dimensions[1].height = 22
    ws.merge_cells('A1:I1')
    title_cell = ws['A1']
    title_cell.value = "物   资  领  料  单"
    title_cell.font = title_font
    title_cell.alignment = center_alignment
    
    # === 写入单位信息行 ===
    ws.row_dimensions[2].height = 27
    ws.merge_cells('A2:I2')
    unit_info = f"使用单位：{new_data['user'] if new_data else '使用单位：'}        领料单号：{'BJ'+new_data['request_id'] if new_data else '领料单号：'}"
    ws['A2'].value = unit_info
    ws['A2'].font = header_font
    ws['A2'].alignment = left_alignment
    
    row_start = 3
    sum_cells = ''
    for address in new_data['addresses']:
        row_index = 0
        # === 写入表头 ===
        ws.row_dimensions[row_start+row_index].height = 30
        headers = ["序号", "物资名称", "规格型号", "单位", "数量", "单价", "金额", "备注（用途）", "领用人"]
        for col, header in enumerate(headers, start=1):
            cell = ws.cell(row=row_start, column=col, value=header)
            cell.font = header_font
            cell.border = border
            cell.alignment = center_alignment
        row_index += 1 
       
        # === 写入数据行 ===
        for i, item in enumerate(address['items'], start=row_start+row_index): 
            ws.row_dimensions[i].height = 30
            # 序号
            ws.cell(row=i, column=1, value=row_index).alignment = center_alignment
            # 物资名称
            ws.cell(row=i, column=2, value=item.get("name", "")).alignment = center_alignment
            # 规格型号
            ws.cell(row=i, column=3, value=item.get("spec", "")).alignment = center_alignment
            # 单位
            ws.cell(row=i, column=4, value=item.get("unit", "")).alignment = center_alignment
            # 数量
            ws.cell(row=i, column=5, value=item.get("count", "")).data_type = 'int'
            ws.cell(row=i, column=5).alignment = center_alignment
            # 单价
            ws.cell(row=i, column=6).number_format = "￥#,##0.00"
            ws.cell(row=i, column=6, value=str(item.get("price_per_unit", ""))).data_type = 'int'
            ws.cell(row=i, column=6).alignment = center_alignment
            # 金额（公式）
            ws.cell(row=i, column=7, value=f"=E{i}*F{i}").alignment = center_alignment
            ws.cell(row=i, column=7).number_format = "￥#,##0.00"
            # 备注
            ws.cell(row=i, column=8, value=address.get("address", ""))
            # 领用人
            ws.cell(row=i, column=9, value="").alignment

            row_index += 1
            
            # 应用样式
            for col in range(1, 10):
                ws.cell(row=i, column=col).font = normal_font
                ws.cell(row=i, column=col).border = border
    
        # === 写入小计行 ===
        last_row = row_start+row_index
        row_index += 1
        ws.row_dimensions[last_row].height = 30
        ws.cell(row=last_row, column=1, value="").border = border
        ws.merge_cells(f'B{last_row}:D{last_row}')
        ws.cell(row=last_row, column=2, value="小计（金额）").font = header_font
        ws.cell(row=last_row, column=7, value=f"=SUM(G{row_start+1}:G{last_row-1})").font = header_font
        ws.cell(row=last_row, column=7).number_format = "￥#,##0.00"
        print(ws.cell(row=last_row, column=7).value)
        for col in [2,5,6,7,8,9]:
            ws.cell(row=last_row, column=col).border = border
        
        ws.merge_cells(f'H{row_start+1}:H{last_row-1}')
        ws.merge_cells(f'I{row_start+1}:I{last_row-1}')
        ws.merge_cells(f'B{last_row}:D{last_row}')
        # ws.merge_cells(f'E{last_row}:I{last_row}') # 小记行不再合并
        ws.cell(row=last_row, column=2).alignment = right_alignment
        ws.cell(row=last_row, column=2).border = border

        ws.cell(row=last_row, column=7).alignment = center_alignment
        # ws.cell(row=last_row, column=7).border = border

        # 列：备注
        # ws.cell(row=row_start+1, column=8).alignment = center_alignment
        ws.cell(row=row_start+1, column=8).alignment = Alignment(wrap_text=True, 
                                                                 horizontal="center",
                                                                 vertical="center")

        # 计算合计
        sum_cells += f'G{last_row}+'
        
        row_start += row_index

    # === 设置总计 ===
    ws.row_dimensions[row_start].height = 15
    ws.cell(row=row_start, column=1, value="合计（金额）")
    ws.cell(row=row_start, column=7, value=f"=SUM({sum_cells[:-1]})")
    ws.cell(row=row_start, column=7).number_format = "￥#,##0.00"
    ws.merge_cells(f'A{row_start}:D{row_start}')
    ws[f'A{row_start}'].font = header_font
    for i in range(1, 9):
        ws.cell(row=row_start, column=i).border = border
    
    ws[f'A{row_start}'].alignment = right_alignment
    
    ws[f'G{row_start}'].font = header_font
    for row in ws[f'A{row_start}:I{row_start}']:
        for cell in row:
            cell.border = border
    ws[f'G{row_start}'].alignment = center_alignment

    row_start += 1
    # === 写入签字行 ===
    ws.row_dimensions[row_start].height = 15
    signature_row = row_start
    ws.merge_cells(f'A{signature_row}:B{signature_row}')
    ws.cell(row=signature_row, column=1, value="后勤服务中心：").font = normal_font
    ws.merge_cells(f'C{signature_row}:D{signature_row}')
    ws.cell(row=signature_row, column=3, value="乙方项目负责人：").font = normal_font
    ws.merge_cells(f'E{signature_row}:G{signature_row}')
    ws.cell(row=signature_row, column=5, value="场所负责人：").font = normal_font
    ws.cell(row=signature_row, column=5).alignment = center_alignment
    ws.merge_cells(f'H{signature_row}:I{signature_row}')
    ws.cell(row=signature_row, column=8, value="保管员：").font = normal_font
    for col in range(1, 10):
        ws.cell(row=signature_row, column=col).border = bottom_border
        if col == 1:
            ws.cell(row=signature_row, column=col).border = BL_border
        if col == 9:
            ws.cell(row=signature_row, column=col).border = BR_border
    # === 设置列宽 ===
    # column_widths = [6.07, 13, 9.4, 7.4, 7.4, 7.47, 7.73, 12.67, 10.07]
    column_widths = [6.07+0.69, 13+0.69, 9.4+0.69, 7.4+0.69, 7.4+0.69, \
                     7.47+0.69, 7.73+0.69, 12.67+0.69, 10.07+0.69]
    for i, width in enumerate(column_widths, start=1):
        ws.column_dimensions[get_column_letter(i)].width = width
    
    # === 保存文件 ===
    wb.save(output_path)


def create_material_template_0906(output_path, new_data=None):
    """
    创建物资领料单模板
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "物资领料单"
    
    # === 设置样式 ===
    title_font = Font(name='宋体', size=14, bold=True)
    header_font = Font(name='宋体', size=11, bold=True)
    normal_font = Font(name='宋体', size=11)
    border = Border(left=Side(style='thin'), 
                   right=Side(style='thin'), 
                   top=Side(style='thin'), 
                   bottom=Side(style='thin'))
    bottom_border = Border(bottom=Side(style='thin'))
    BR_border = Border(bottom=Side(style='thin'), right=Side(style='thin'))
    BL_border = Border(bottom=Side(style='thin'), left=Side(style='thin'))

    center_alignment = Alignment(horizontal='center', vertical='center')
    right_alignment = Alignment(horizontal='right', vertical='center')
    left_alignment = Alignment(horizontal='left', vertical='center')
    
    # === 写入标题行 ===
    ws.row_dimensions[1].height = 22
    ws.merge_cells('A1:F1')
    title_cell = ws['A1']
    title_cell.value = "物   资  领  料  单"
    title_cell.font = title_font
    title_cell.alignment = center_alignment
    
    # === 写入单位信息行 ===
    ws.row_dimensions[2].height = 27
    ws.merge_cells('A2:F2')
    unit_info = f"使用单位：{new_data['user'] if new_data else '使用单位：'}        领料单号：{'BJ'+new_data['request_id'] if new_data else '领料单号：'}"
    ws['A2'].value = unit_info
    ws['A2'].font = header_font
    ws['A2'].alignment = left_alignment
    
    row_start = 3
    sum_cells = ''
    for address in new_data['addresses']:
        row_index = 0
        # === 写入表头 ===
        ws.row_dimensions[row_start+row_index].height = 30
        headers = ["序号", "物资名称", "单位", "数量", "备注（用途）", "领用人"]
        for col, header in enumerate(headers, start=1):
            cell = ws.cell(row=row_start, column=col, value=header)
            cell.font = header_font
            cell.border = border
            cell.alignment = center_alignment
        row_index += 1 
       
        # === 写入数据行 ===
        for i, item in enumerate(address['items'], start=row_start+row_index): 
            ws.row_dimensions[i].height = 30
            # 序号
            ws.cell(row=i, column=1, value=row_index).alignment = center_alignment
            # 物资名称
            ws.cell(row=i, column=2, value=item.get("name", "")).alignment = center_alignment
            # 单位
            ws.cell(row=i, column=3, value=item.get("unit", "")).alignment = center_alignment
            # 数量
            ws.cell(row=i, column=4, value=item.get("count", "")).data_type = 'int'
            ws.cell(row=i, column=4).alignment = center_alignment
            # 备注
            ws.cell(row=i, column=5, value=address.get("address", "")).alignment = center_alignment
            # 领用人
            ws.cell(row=i, column=6, value="").alignment = center_alignment


            row_index += 1
            
            # 应用样式
            for col in range(1, 7):
                ws.cell(row=i, column=col).font = normal_font
                ws.cell(row=i, column=col).border = border
    
        # === 写入小计行 ===
        last_row = row_start+row_index
        row_index += 1
        ws.row_dimensions[last_row].height = 30
        for col in range(1, 7):
            ws.cell(row=last_row, column=col).border = border

        # ws.cell(row=last_row, column=1, value="").border = border
        ws.merge_cells(f'B{last_row}:C{last_row}')
        ws.cell(row=last_row, column=2, value="小计（金额）").font = header_font

        # 合并地点和领用人单元格 
        ws.merge_cells(f'E{row_start+1}:E{last_row-1}')
        ws.merge_cells(f'F{row_start+1}:F{last_row-1}')
        ws.merge_cells(f'B{last_row}:C{last_row}')
        
        ws.cell(row=last_row, column=2).alignment = right_alignment
        row_start += row_index

    # === 设置总计 ===
    ws.row_dimensions[row_start].height = 15
    for i in range(1, 7):
        ws.cell(row=row_start, column=i).border = border
    ws.merge_cells(f'A{row_start}:C{row_start}')
    ws.cell(row=row_start, column=1, value="合计（金额）")
    ws[f'A{row_start}'].font = header_font
    ws[f'A{row_start}'].alignment = right_alignment
    
    row_start += 1
    # === 写入签字行 ===
    ws.row_dimensions[row_start].height = 15
    signature_row = row_start
    ws.merge_cells(f'A{signature_row}:B{signature_row}')
    ws.cell(row=signature_row, column=1, value="乙方项目负责人：").font = normal_font
    ws.cell(row=signature_row, column=3, value="场所负责人：").font = normal_font
    ws.cell(row=signature_row, column=5, value="保管员：").font = normal_font
    ws.cell(row=signature_row, column=5).alignment = center_alignment
    for col in range(1, 7):
        ws.cell(row=signature_row, column=col).border = bottom_border
        if col == 1:
            ws.cell(row=signature_row, column=col).border = BL_border
        if col == 6:
            ws.cell(row=signature_row, column=col).border = BR_border
    
    # === 设置列宽 ===
    column_widths = [10.92, 17.77, 10.31, 8.92, 18.08, 13.62]
    for i, width in enumerate(column_widths, start=1):
        ws.column_dimensions[get_column_letter(i)].width = width
    
    # === 保存文件 ===
    wb.save(output_path)

create_material_template=create_material_template_0906
